﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    /// <summary>
    /// 
    /// </summary>
    public class LoteGuiaRepository : IRepository<LoteGuia>
    {
        private IDbCommand Command { get; set; }

        public LoteGuiaRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public LoteGuia GetById(int id)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idLoteGuia, numeroLote, idTipoGuia, idOperadoraPrestador FROM tbLoteGuia WHERE idLoteGuia=" + id;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    LoteGuia loteGuia = new LoteGuia()
                    {
                        ID = int.Parse(reader["idLoteGuia"].ToString()),
                        NumeroLote = reader["numeroLote"].ToString(),
                        TipoGuia = ((TipoGuia)(Convert.ToInt32(reader["idTipoGuia"]))),
                        IDOperadoraPrestador = int.Parse(reader["idOperadoraPrestador"].ToString())
                    };
                    return loteGuia;
                }
                else
                {
                    throw new Exception("LoteGuia não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<LoteGuia> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idLoteGuia, numeroLote, idTipoGuia, idOperadoraPrestador FROM tbLoteGuia";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<LoteGuia> list = new List<LoteGuia>();
                while (reader.Read())
                {
                    LoteGuia loteGuia = new LoteGuia()
                    {
                        ID = int.Parse(reader["idLoteGuia"].ToString()),
                        NumeroLote = reader["numeroLote"].ToString(),
                        TipoGuia = (TipoGuia)int.Parse(reader["idTipoGuia"].ToString()),
                        IDOperadoraPrestador = int.Parse(reader["idOperadoraPrestador"].ToString())
                    };
                    list.Add(loteGuia);
                }
                return list.AsQueryable<LoteGuia>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(LoteGuia entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "INSERT INTO tbLoteGuia (numeroLote, idTipoGuia, idOperadoraPrestador) VALUES ('" +
                    entity.NumeroLote + "'," + (int)entity.TipoGuia + "," + entity.IDOperadoraPrestador +  ")";
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();

                Command.CommandText = "SELECT @@IDENTITY";

                return int.Parse(Command.ExecuteScalar().ToString());
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(LoteGuia entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbLoteGuia WHERE idLoteGuia=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(LoteGuia entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "UPDATE tbLoteGuia SET numeroLote='" + entity.NumeroLote + "', idTipoGuia=" + (int)entity.TipoGuia +
                    ", idOperadoraPrestador=" + entity.IDOperadoraPrestador + "' WHERE idLoteGuia=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
